package com.f.util;


import cn.hutool.core.io.FileUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;

/**
 * 可以用来获取数据库中所有的表名，以及表对应的字段，字段类型
 */
public class DBUtil {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        HashMap<String, Object> computer_mall = (HashMap<String, Object>) getDatabaseNameToTableNameAndColumnName("cobra");
        String cityIdAdd = "ALTER TABLE cobra.#{tableName} ADD COLUMN city_id bigint(20) NULL COMMENT '城市id';";
        String hospitalIdAdd = "ALTER TABLE cobra.#{tableName} ADD COLUMN hospital_id bigint(20) NULL COMMENT '医院id';";
        String hospitalNameAdd = "ALTER TABLE cobra.#{tableName} ADD COLUMN hospital_name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '医院名称';";
        Set<String> tableNames = computer_mall.keySet();
        System.out.println("一共有表：" + tableNames.size() + "张");
        ArrayList<String> list = new ArrayList<>();
        for (String tableName : tableNames) {
            System.out.println("===========================\r\n");
            System.out.println("表名：" + tableName);

            // <字段名,类型>  HashMap 集合
            HashMap<String, String> columnNames = (HashMap<String, String>) computer_mall.get(tableName);
            if (!columnNames.containsKey("hospital_id")) {
                list.add(hospitalIdAdd.replace("#{tableName}", tableName));
            }
            if (!columnNames.containsKey("hospital_name")) {
                list.add(hospitalNameAdd.replace("#{tableName}", tableName));
            }
            if (!columnNames.containsKey("city_id")) {
                list.add(cityIdAdd.replace("#{tableName}", tableName));
            }

            Set<String> columns = columnNames.keySet();


            for (String column : columns) {
                System.out.println("字段名：" + column + " 类型：" + columnNames.get(column));
            }

            System.out.println("===========================\r\n");
        }

        FileUtil.writeUtf8Lines(list,"E:\\temp\\add.sql");

    }

    public static Map getDatabaseNameToTableNameAndColumnName(String databaseName) throws SQLException, ClassNotFoundException {

        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://rm-wz9bxox3llwz16ftl.mysql.rds.aliyuncs.com:3306/" + databaseName;
        String username = "lgrmyy";
        String password = "5gHFK5g6bpc5T2gT";
        Map<String, Object> tableNameMap = new HashMap<>();


        //加载驱动
        Class.forName(driver);

        //获得数据库连接
        Connection connection = DriverManager.getConnection(url, username, password);
        //获得元数据
        DatabaseMetaData metaData = connection.getMetaData();
        //获得表信息
        ResultSet tables = metaData.getTables(null, null, null, new String[]{"TABLE"});

        while (tables.next()) {
            Map<String, String> columnNameMap = new HashMap<>(); //保存字段名

            //获得表名
            String table_name = tables.getString("TABLE_NAME");
            //通过表名获得所有字段名
            ResultSet columns = metaData.getColumns(null, null, table_name, "%");
            //获得所有字段名
            while (columns.next()) {
                //获得字段名
                String column_name = columns.getString("COLUMN_NAME");
                //获得字段类型
                String type_name = columns.getString("TYPE_NAME");

                columnNameMap.put(column_name, type_name);
            }

            tableNameMap.put(table_name, columnNameMap);

        }

        return tableNameMap;
    }
}
